#encoding: utf-8
=begin

Usage:      This ruby file use to fix data errors in crmeb caused by bugs

=end

load 'rds_api.rb'
load 'crmeb_utils.rb'

#ERR1: 实际支付与用户侧订单显示支付情况不符

#2020.7.11 用户ID 915 报告实际支付和订单统计不符的情况
#订单号23，实际扣除的余额及积分为434.28及10.91（总额为445.19），最后的订单记录中为（392.67+9.86）, 手工复算订单应付金额为后者
#以下过程用于发现存在类似问题的订单,方法是比较数据栏pay_price与misc栏中pay_price（计算邮费时产生的）的差异
def err1a_getPotentialWrongOrderIds
  questioned_orders = 0
  qoia = [] #用于存储问题订单号的数组
  queryRds("select * from crmeb.eb_store_order").each do |r|
    misc = JSON.parse r['misc']
    if misc['pay_price'].to_i !=  r['pay_price'].to_i
      questioned_orders += 1
      qoia += [r['id']]
      #print "ERROR ID:", r['id'], ' ', r['order_id'], ' ', r['order_code'], "\n"
      #print "source total_price, total_postage, deduction_price, pay_price, pay_postage\n"
      #print "order: ", sprintf("%.2f",r['total_price']), ' ', sprintf("%.2f",r['total_postage']), ' ', sprintf("%.2f",r['deduction_price']), ' ', sprintf("%.2f",r['pay_price']), ' ', sprintf("%.2f",r['pay_postage']), "\n"
      total_postage = 0.0
      tempPostages = misc['tempPosagets'] if ! misc['tempPosagets'].nil?
      tempPostages = misc['tempPostages'] if ! misc['tempPostages'].nil?
      tempPostages.each { |line, lp| total_postage += lp.to_f }
      #print "misc:  ", misc['total_price'], ' ', total_postage, ' ', misc['deduction_price'], ' ', misc['pay_price'], ' ', misc['pay_postage'], "\n"
    end
  end
  return qoia
end
#在写过程中，发现fc4/app/models/store/StoreOrder.php中将tempPostages写成tempPosagets了，这样split-order在计算时将无法正确获得运费值,热修改
#之前的订单数量记录为78,可能受影响的范围为37,将针对misc中存在tempPosagets记录的订单做复原和恢复。

def err1a_getSplittedOrderIds
  oia = [] #用于存储问题订单号的数组
  queryRds("select * from crmeb.eb_store_order where order_code like '%FC%'").each do |r|
    oia += [r['id']]
  end
  return oia
end

def err1a_get_orders_group
  grouped_orders = {}
  getPrimaryOid = {}
  orders={}
  res = queryRds "select * from crmeb.eb_store_order where order_code like '%FC%'"
  res.each do |order|
    oid = order['id']
    primary_oid = order['order_code'][8..9].to_i
    temp_id = order['order_code'].split('S')[1].to_i
    misc = JSON.parse order['misc']
    temp_postage = 0
    begin
      temp_postage = misc['tempPosagets'][temp_id.to_s].to_f if !misc['tempPosagets'].nil?
      temp_postage = misc['tempPostages'][temp_id.to_s].to_f if !misc['tempPostages'].nil?
    rescue => e
      puts "ERROR >>>>>> tempPostage empty: oid##{oid}"
    end
    order.store("temp_postage",temp_postage);
    orders.store(oid,order)
    getPrimaryOid.store(oid,primary_oid)
    if grouped_orders[primary_oid].nil?
      grouped_orders.store(primary_oid,[oid])
    else
      current_set = grouped_orders[primary_oid]
      current_set += [oid]
      grouped_orders.store(primary_oid,current_set)
    end
  end
  relatedOrders = {}
  getPrimaryOid.each do |oid, pid|
    relatedOrders.store(oid,{"oids"=>grouped_orders[pid],"order_self"=>orders[oid]})
  end
  return relatedOrders
end

#total_price 应付商品总价
#total_postage 运费
#pay_price 实际支付（余额或微信）
#pay_postage --- 原生订单未使用
#deduction_price 积分抵扣
#pay_price = total_price + total_postage - deduction_price
def err1a_fix #if using err1a_fix! will acutally update
  #qoia = err1a_getPotentialWrongOrderIds #生成问题订单号数组表
  qoia = err1a_getSplittedOrderIds
  relatedOrders = err1a_get_orders_group
  negative_counter = 0
  sqls = []
  qoia.each do |qoi|

      this_order = relatedOrders[qoi]['order_self']
      oidsa = relatedOrders[qoi]['oids']
      misc = JSON.parse this_order['misc']

      total_price_old = this_order['total_price'].round(2)
      total_price_new = calcTruePriceByOrder this_order
      neg1 = total_price_old.round(2).to_f != total_price_new.round(2).to_f ? true : false #不这么干的话，old有可能总是个科学计数法, new 可能会是'10.9'

      total_postage_old = this_order['total_postage']
      total_postage_new = this_order['temp_postage']
      neg2 = total_postage_new.round(2) > 0 && total_postage_old.round(2) == 0 ? true : false

      total_cost_new = total_price_new + total_postage_new
      total_cost_old = total_price_old + total_postage_old
      neg3 = total_cost_new.round(2) > 0 && total_cost_old.round(2) == 0 ? true : false

      whole_cost = misc['total_price'].to_f + misc['pay_postage'].to_f
      whole_deduction = misc['deduction_price']
      whole_integral = (whole_deduction * 100 ).to_i

      use_integral_old = this_order['use_integral']
      use_integral_new = whole_cost < 0.01 ? 0 : (whole_integral * total_cost_new / whole_cost ).round(2)
      neg4 = use_integral_new.round(2) > 0 && use_integral_old.round(2) == 0 ? true : false

      deduction_price_old = this_order['deduction_price']
      deduction_price_new = whole_cost < 0.01 ? 0 : ( whole_deduction * total_cost_new / whole_cost ).round(2)
      neg5 = deduction_price_new.round(2) > 0 && deduction_price_old.round(2) == 0 ? true : false

      pay_price_old = this_order['pay_price']
      pay_price_new = total_cost_new - deduction_price_new
      neg6 = pay_price_new.round(2) > 0 && pay_price_old.round(2) == 0 ? true : false

      pay_postage_old = this_order['pay_postage']
      pay_postage_new = total_postage_new
      neg7 = pay_postage_new.round(2) > 0 && pay_postage_old.round(2) == 0 ? true : false

      negative =  neg1 || neg2 || neg3 || neg4 || neg5 || neg6 || neg7
      negative_counter += 1 if negative
      if negative
        puts "                                                  oid: #{sprintf('%3d',qoi)}"
        puts "      total_price_old: #{sprintf('%6.2f',    total_price_old.round(2))},     total_price_new: #{sprintf('%6.2f',total_price_new.round(2))} #{ neg1 ? '<<<' : '' }"
        puts "    total_postage_old: #{sprintf('%6.2f',  total_postage_old)},   total_postage_new: #{sprintf('%6.2f',total_postage_new)} #{neg2 ? '<<<' : '' }"
        puts "       total_cost_old: #{sprintf('%6.2f',     total_cost_old)},      total_cost_new: #{sprintf('%6.2f',total_cost_new)} #{neg3 ? '<<<' : '' }"
        puts "     use_integral_old: #{sprintf('%6.2f',   use_integral_old)},    use_integral_new: #{sprintf('%6.2f',use_integral_new)} #{neg4 ? '<<<' : '' }"
        puts "  deduction_price_old: #{sprintf('%6.2f',deduction_price_old)}, deduction_price_new: #{sprintf('%6.2f',deduction_price_new)} #{neg5 ? '<<<' : '' }"
        puts "        pay_price_old: #{sprintf('%6.2f',      pay_price_old)},       pay_price_new: #{sprintf('%6.2f',pay_price_new)} #{neg6 ? '<<<' : '' }"
        puts "      pay_postage_old: #{sprintf('%6.2f',    pay_postage_old)},     pay_postage_new: #{sprintf('%6.2f',pay_postage_new)} #{neg7 ? '<<<' : '' }"
        if oidsa.size > 1
          print oidsa.to_json, " price: ", misc['total_price'], " postage: ", misc['pay_postage'], " paid: ", misc['pay_price'], " deduction: ", misc['deduction_price'].to_s, "\n"
        end
        sql = "update crmeb.eb_store_order set 
                total_price = #{total_price_new.round(2)},
                total_postage = #{total_postage_new.round(2)}, 
                pay_price = #{pay_price_new.round(2)},
                pay_postage = #{pay_postage_new.round(2)}, 
                deduction_price = #{deduction_price_new.round(2)},
                use_integral = #{use_integral_new.round(2)} 
              where id = #{qoi};"
         sqls += [sql]
      end 
  end
  puts "total negative: #{negative_counter}"
  if sqls.size > 0
    save_fix_sqls sqls, "err1a-fix"
  end
end

#err1a_fix

#待跟进，发现oid#62, tempPosagets为[]
#待跟进，oid#79,拆分时未获得temp_id

#待跟进，发现[41,42],[77,78]修正后模板6订单收取了运费
#订单41,42,77,78已退款

#ERR1B

#解决库存显示不准确的问题，在读源码时，减库存过程
#app/models/store/StoreProduct::decProductStock调用前会传入unqiue
#unique存在的时候，既减AttrValue又减Stock，unique不存在的时候只减stock
#所以我们要看一下什么样的商品属性是购物车里没有存unique的

#是否每一个商品都有一个或多个对应的product_attr记录
#每一项都有，这就解释了AttrValue和Stock能正确变化的原因
def check_order_without_unique
  res = queryRds "select * from crmeb.eb_store_order_cart_info"
  counter = 0
  res.each do |r|
    cart_info = JSON.parse r['cart_info']
    uni1 = cart_info['product_attr_unique'].to_s
    uni2 = cart_info['productInfo']['attrInfo']['unique'].to_s
    if uni1.empty? || uni2.empty? || uni1 != uni2
      puts "#{r['id']}: #{uni1} vs #{uni2}" 
      counter += 1
    end
  end
  puts "total: #{counter}"
end

def check_product_without_unique
  res = queryRds "select id from crmeb.eb_store_product"
  counter = 0
  res.each do |r|
  end
end
#check_order_without_unique

#实际库存有问题的主要是被自动取消或用户删除的订单商品，重点关注这些商品基本就可以了，因为有不少应该手工调过，所以不能用程序恢复，导一张相关表出来。
def err2_check_list
  counter = 0
  res = queryRds "select * from crmeb.eb_store_order_cart_info where oid in ( select id from crmeb.eb_store_order where is_del = 1 )"
  qnHash = {} #记录名字
  qqHash = {} #记录数量
  res.each do |r|
    product_id = r['product_id']
    cart_info = JSON.parse r['cart_info']
    product_name = cart_info['productInfo']['store_name']
    qnHash.store(product_id, product_name)
    counter += 1
    if qqHash[product_id].nil?
      qqHash.store product_id, cart_info['cart_num']
    else
      qq = qqHash[product_id]
      qq += cart_info['cart_num']
      qqHash.store product_id, qq
    end
  end
  qqHash.each do |pid, qty|
    puts "[#{sprintf('%2d',qqHash[pid])}] #{qnHash[pid]} - ##{pid}"
  end
  puts "total: #{counter}"
end
#err2_check_list #run it as: #ruby fix-crmeb.rb > fix/err2_checklist.txt


def format_iconfont
  dir_name = OS.windows? ? ".\\fix\\iconfont.wxss" : "./fix/iconfont.wxss"
  index = 0
  IO.readlines(dir_name).each do |line|
    if line.include? 'icon-'
      index += 1
      the_str = "<view>#{index} icon_name <text class='iconfont icon_name'></text></view>"
      icon_name = line.split(':')[0].gsub('.','')
      puts the_str.gsub('icon_name',icon_name)
    end
  end
end
#format_iconfont #ruby fix-crmeb.rb > fix/font.txt

#去除商品属性中包含目录2（超值推荐）的残留信息
def remove_cat2_from_product
  sql = "select id, cate_id, store_name from crmeb.eb_store_product where cate_id like '2,%' or cate_id like '%,2,%' or cate_id like '%,2' or cate_id like '%,2,%';"
  usqls = []
  queryRds(sql).each do |q|
    pid = q['id']
    cate_id = q['cate_id']
    cate_id = (cate_id.split(',').uniq-['2']).join(',')
    usql = "update crmeb.eb_store_product set cate_id = '#{cate_id}' where id = #{pid};"
    usqls += [usql]
  end
  commitTrans usqls
end
#remove_cat2_from_product

#将创建代销商品属性时未考虑type区别导致时错误创建的属性
def del_wrong_pav_1
  pav_uniques = []
  sql = "select av.product_id, p.store_name, av.`type`, av.`unique`
    from crmeb.eb_store_product_attr_value av
    left join crmeb.eb_store_product p on (p.id = av.product_id)
    where p.source_pid > 0 and av.`type` > 0;"
  queryRds(sql).each { |r| pav_uniques += [r['unique']]}

  usqls = []
  pav_uniques.each { |pu| usqls += ["delete from crmeb.eb_store_product_attr_value where `unique` = '#{pu}';"] }
  if usqls.size > 0
    append_lines_to_fix usqls, 'del_wrong_pav_1.log'
    commitTrans usqls
  end
end
#del_wrong_pav_1 #done

def get_unqualified_consign_products

  cph = {} #consign products hash
  source_pids = []
  query = queryRds "select id, source_pid from crmeb.eb_store_product where source_pid > 0"
  query.each do |q| 
    consign_pid = q['id']
    source_pid  = q['source_pid']
    source_pids += [source_pid]
    consign_uniques = []
    source_uniques = []

    queryRds("select `unique` from crmeb.eb_store_product_attr_value where product_id = #{consign_pid} and `type` = 0;").each { |qr| consign_uniques += [qr['unique']] }
    queryRds("select `unique` from crmeb.eb_store_product_attr_value where product_id = #{source_pid}  and `type` = 0;").each { |qr| source_uniques += [qr['unique']] }

    values = {consign_pid: consign_pid, consign_uniques: consign_uniques, source_pid: source_pid, source_uniques: source_uniques}
    cph.store consign_pid, values

    if values[:source_uniques].size != values[:consign_uniques].size
      puts "存在不一致的配对商品 consign##{consign_pid} source##{source_pid} - 源商品本身是代销商品 - 请手工检查"
      exit
    end

  end
  puts "consign products total: #{cph.size} "

  if source_pids.size > 1
    check = "select count(*) as total from crmeb.eb_store_product where id in (#{source_pids.join(',')}) and source_pid > 0;"
    query = queryRds check
    query.each do |q|
      if q['total'] > 0
        puts '存在非法的源商品ID - 源商品本身是代销商品 - 请手工检查'
        exit
      end
      break
    end
  end

  qualified = []
  query = queryRds "select product_id from crmeb.eb_store_product_attr_value where `unique` like '%-%'"
  query.each { |q| qualified += [q['product_id']] }
  qualified = qualified.uniq
  puts "qualified products total: #{qualified.size} "

  qualified.size.times { |idx| cph.delete(qualified[idx]) }

  puts "unqualified consign products total: #{cph.size} "

  return cph

end

#迁移代销商品的属性值unique为对应原型商品的unique-spid
#涉及的表有： 
#eb_pickup_book: json
#eb_store_cart: product_attr_unique
#eb_store_order_cart_info: cart_info
#eb_store_product_attr_value: `unique`
def fix_consign_unique cph # cph: {consign_pid=> source_pid}

  sqls = []

  cph.each do |consign_pid, value|

    #if consign_pid == 2266 #test example consign 2266, source 2

      p consign_pid
      p consign_uniques = value[:consign_uniques]

      p source_pid = value[:source_pid]
      p source_uniques = value[:source_uniques]

      target_uniques = []
      source_uniques.each { |uq| target_uniques += [uq+'-'+source_pid.to_s] }
      p target_uniques

      consign_uniques.size.times do |idx|

        consign_unique = consign_uniques[idx]
        source_unique  = source_uniques[idx]
        target_unique  = target_uniques[idx]

        #查找并替代
        sqls += [ "#update #{consign_pid} #{source_pid} #{consign_unique} #{source_unique} #{target_unique};" ]
        sqls += [ "update crmeb.eb_pickup_book set json = replace (json,'#{consign_unique}','#{target_unique}') where json like '%#{consign_unique}%';" ]
        sqls += [ "update crmeb.eb_store_cart set product_attr_unique = '#{target_unique}' where product_attr_unique = '#{consign_unique}';" ]
        sqls += [ "update crmeb.eb_store_order_cart_info set cart_info = replace (cart_info,'#{consign_unique}','#{target_unique}') where cart_info like '%#{consign_unique}%';" ]
        sqls += [ "update crmeb.eb_store_product_attr_value set `unique` = '#{target_unique}' where `unique` = '#{consign_unique}';" ]

      end

    #end #test example

  end

  append_lines_to_fix sqls, 'fix_consign_unique_2.log'
  #commitTrans sqls

end
#ucph = get_unqualified_consign_products
#fix_consign_unique ucph

#因为上一表名错误eb_store_order_cart_info叫成了eb_order_cart_info,手工将命令保存导fix/fix_c3.sql中
def run_fix_c3_sql
  sqls = IO.readlines "fix_c3.sql"
  commitTrans sqls
end
#run_fix_c3_sql

#get_unqualified_consign_products

